package com.realtyManage.springboot.Dao;


import com.realtyManage.springboot.Model.Do.HouseDynamic;
import com.realtyManage.springboot.Model.Do.ProjectApartment;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import javax.transaction.Transactional;
import java.util.List;

/**
 * 楼盘动态
 *
 * @author 杭州素色网络科技有限公司
 * @since 2019-10-14
 */

@Repository
public interface ProjectApartmentRepository extends JpaRepository<ProjectApartment, Integer>, JpaSpecificationExecutor<ProjectApartment> {

    @Transactional
    @Modifying
    @Query("update ProjectApartment a set " +
            "a.houseScale = CASE WHEN :#{#projectApartment.houseScale} IS NULL THEN a.houseScale ELSE :#{#projectApartment.houseScale} END ," +
            "a.propertyType = CASE WHEN :#{#projectApartment.propertyType} IS NULL THEN a.propertyType ELSE :#{#projectApartment.propertyType} END ," +
            "a.apartmentImg = CASE WHEN :#{#projectApartment.apartmentImg} IS NULL THEN a.apartmentImg ELSE :#{#projectApartment.apartmentImg} END ," +
            "a.prospectus = CASE WHEN :#{#projectApartment.prospectus} IS NULL THEN a.prospectus ELSE :#{#projectApartment.prospectus} END ," +
            "a.apartmentNumber = CASE WHEN :#{#projectApartment.apartmentNumber} IS NULL THEN a.apartmentNumber ELSE :#{#projectApartment.apartmentNumber} END ," +
            "a.architectureArea = CASE WHEN :#{#projectApartment.architectureArea} IS NULL THEN a.architectureArea ELSE :#{#projectApartment.architectureArea} END ," +
            "a.apartmentAveragePrice = CASE WHEN :#{#projectApartment.apartmentAveragePrice} IS NULL THEN a.apartmentAveragePrice ELSE :#{#projectApartment.apartmentAveragePrice} END ," +
            "a.apartmentLabel = CASE WHEN :#{#projectApartment.apartmentLabel} IS NULL THEN a.apartmentLabel ELSE :#{#projectApartment.apartmentLabel} END ," +
            "a.apartmentType = CASE WHEN :#{#projectApartment.apartmentType} IS NULL THEN a.apartmentType ELSE :#{#projectApartment.apartmentType} END " +
            "where a.id = :#{#projectApartment.id}")
    int updateProjectApartment(@Param("projectApartment") ProjectApartment projectApartment);

    @Modifying
    @Query(value = "delete from project_apartment where id in (?1)",nativeQuery = true)
    int deleteProjectApartment(List ids);

    @Query(value = "SELECT \"全部\",COUNT(1) sum FROM `project_apartment`\n" +
            "UNION ALL\n" +
            "SELECT house_type,COUNT(1) num FROM `project_apartment` GROUP BY house_type",nativeQuery = true)
    List getCountHouseScale();

    @Query(value = "SELECT property_tye FROM house_detail GROUP BY property_tye",nativeQuery = true)
    List getCountPropertyType();


    @Query(value = "SELECT prospectus FROM project_apartment WHERE apartment_number = ?1 LIMIT 1",nativeQuery = true)
    String getImg(String apartmentNumber);

    @Query(value = "SELECT * FROM project_apartment WHERE apartment_number = ?1 AND house_type = ?2 AND apartment_img LIKE %?3% LIMIT 1",nativeQuery = true)
    List<ProjectApartment> getBsImg(String apartmentNumber,String houseType,String apartmentImg);

    @Query(value = "SELECT COUNT(1) FROM project_apartment WHERE property_type = ?1",nativeQuery = true)
    String getCountPropertyType(String propertyType);
}
